package com.reef.dao;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.reef.entity.Lend_book;

public interface LendBookRepository extends JpaRepository<Lend_book, Integer>,JpaSpecificationExecutor<Lend_book>{
	/**
	 * 多条件动态查询（借阅表、图书表、学生表 ） 
	 */
	
	/*
	 * @Query(value =
	 * "select lb.sernum,rea.name,b.name,lb.lenddate,lb.backdate,lb.isoverdue,lb.overduefees from lend_book lb,reader rea,book b where 1=1 and lb.bookid=b.book_id and lb.readerid=rea.r_id "
	 * + "and lb.sernum=600001 and rea.name='赵云' and lb.isoverdue='否'" ,nativeQuery
	 * = true ) List<Lend_book> selByIdAnd();
	 */
	/**
	 * 归还书籍
	 */
	@Transactional
	@Modifying
	@Query(value = "update lend_book set realdate=?1, isoverdue= case when (backdate-realdate)>0 then '否'  else '是' end ,\r\n" + 
			"overduefees= case when isoverdue='是'  then  (backdate-realdate)*-6 else 0 end,isback=?2\r\n" + 
			"where sernum = ?3",nativeQuery = true)
	int updateByBackBookDate(String realdate,int isback,int sernum);
}
